<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Purchase_model
 *
 * @author peerawatj20
 */
class purchase_model extends CI_Model {

    private $_table_purchase = 'vpc_purchase';
    private $_table_purchase_order = 'vpc_purchase_order';
    private $_table_cat_stock = 'vpc_catalog_autopart';
    private $_table_stock = 'vpc_stock';

    public function get_last_data_repair() {
        $this->db->order_by('id', 'desc');
        $this->db->limit(1);
        $query = $this->db->get_where($this->_table_purchase);
        return $query->result();
    }

    public function update_stock($id, $stock_qty, $stock_price) {
        $arr_stock = array('stock_qty' => $stock_qty,
            'stock_price' => $stock_price,
            'updated_by' => $this->session->userdata('username'),
            'updated_date' => date("Y-m-d H:i:s")
        );

        $arr_where = array('id' => $id);

        $can_save = $this->db->update($this->_table_stock, $arr_stock, $arr_where);
        return $can_save;
    }

    public function save_data($created_date) {
        $arr_purchase = array(
            'create_date' => $created_date,
            'created_by' => $this->session->userdata('username'),
            'created_date' => date("Y-m-d H:i:s")
        );

        $can_save = $this->db->insert($this->_table_purchase, $arr_purchase);
        return $can_save;
    }

    public function save_order_data($purchase_id, $locate_purchase, $main_type_id, $sub_type_id, $brand_id, $stock_id, $qty, $price) {
        $arr_purchase = array('purchase_id' => $purchase_id,
            'locate_purchase' => $locate_purchase,
            'main_type_id' => $main_type_id,
            'sub_type_id' => $sub_type_id,
            'brand_id' => $brand_id,
            'stock_id' => $stock_id,
            'qty' => $qty,
            'price' => $price,
            'created_by' => $this->session->userdata('username'),
            'created_date' => date("Y-m-d H:i:s")
        );

        $can_save = $this->db->insert($this->_table_purchase_order, $arr_purchase);
        return $can_save;
    }

    public function count($start_date = NULL, $end_date = NULL) {

        if (!is_null($start_date) && !empty($start_date)) {
            $this->db->where('DATE(created_date) >=', $start_date);
        }
        if (!is_null($end_date) && !empty($end_date)) {
            $this->db->where('DATE(created_date) <=', $end_date);
        }
        $query = $this->db->get_where($this->_table_purchase);
        return count($query->result());
    }

    public function search_list_purchase($start_date = NULL, $end_date = NULL, $limit = 10, $offset = 0) {
        $this->db->select('*');
        $this->db->select('purchase.id as purchase_id');
        $this->db->select('purchase.create_date as purchase_created_date');
        $this->db->select('count(orders.id) as count_list');
        $this->db->select('sum(orders.price*orders.qty) as sum_price');

        if (!is_null($start_date) && !empty($start_date)) {
            $this->db->where('DATE(purchase.create_date) >=', $start_date);
        }
        if (!is_null($end_date) && !empty($end_date)) {
            $this->db->where('DATE(purchase.create_date) <=', $end_date);
        }
        $this->db->join($this->_table_purchase_order . ' as orders', 'orders.purchase_id = purchase.id', 'left');
        $this->db->group_by(array('orders.purchase_id', 'purchase.id'));
        $query = $this->db->get_where($this->_table_purchase . ' as purchase', NULL, $limit, $offset);
        return $query->result();
    }

    public function get_purchase_by_id($id) {
//        $this->db->select('*');
        $this->db->select('purchase.id as purchase_id');
        $this->db->select('purchase.create_date as purchase_created_date');
        $query = $this->db->get_where($this->_table_purchase . ' as purchase', array('purchase.id' => $id));
        return $query->result();
    }

    public function get_order_by_id($id) {
        $this->db->select('*');
        $this->db->select('a.name as main_type_name');
        $this->db->select('b.name as sub_type_name');
        $this->db->select('c.name as brand_name');
        $this->db->join($this->_table_cat_stock . ' as a', 'a.id = orders.main_type_id', 'left');
        $this->db->join($this->_table_cat_stock . ' as b', 'b.id = orders.sub_type_id', 'left');
        $this->db->join($this->_table_cat_stock . ' as c', 'c.id = orders.brand_id', 'left');
        $this->db->join($this->_table_stock . ' as stock', 'stock.id = orders.stock_id', 'left');
        $query = $this->db->get_where($this->_table_purchase_order . ' as orders', array('purchase_id' => $id));
        return $query->result();
    }

}
